#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Database Management System
Handles database operations for the YOLO application
"""

import logging
import sqlite3
import json
from typing import Dict, List, Any, Optional
from datetime import datetime
import threading

logger = logging.getLogger("database_manager")

class DatabaseManager:
    """Database Management Class"""
    
    def __init__(self, db_path: str = "./yolo_apply.db"):
        """Initialize database manager
        
        Args:
            db_path: Path to SQLite database file
        """
        self.db_path = db_path
        self.connection_lock = threading.RLock()
        self.is_initialized = False
        logger.info(f"Database manager initialized with path: {db_path}")
    
    def initialize(self):
        """Initialize database and create tables"""
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                # Create tables
                self._create_tables(cursor)
                
                conn.commit()
                conn.close()
                
            self.is_initialized = True
            logger.info("Database initialized successfully")
            return True
            
        except Exception as e:
            logger.error(f"Error initializing database: {str(e)}")
            return False
    
    def _create_tables(self, cursor: sqlite3.Cursor):
        """Create required database tables"""
        # Camera configuration table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS camera_configs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            camera_id TEXT UNIQUE NOT NULL,
            config_json TEXT NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # Detection results table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS detection_results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            camera_id TEXT NOT NULL,
            timestamp REAL NOT NULL,
            frame_number INTEGER,
            objects_json TEXT NOT NULL,
            summary TEXT
        )
        ''')
        
        # Alerts table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS alerts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            alert_id TEXT UNIQUE NOT NULL,
            camera_id TEXT NOT NULL,
            violation_type TEXT NOT NULL,
            object_id TEXT,
            object_class TEXT,
            message TEXT NOT NULL,
            priority TEXT DEFAULT 'low',
            status TEXT DEFAULT 'active',
            timestamp REAL NOT NULL,
            created_at TEXT NOT NULL,
            acknowledged_at TEXT,
            resolved_at TEXT,
            metadata_json TEXT
        )
        ''')
        
        # System configuration table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS system_config (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            config_key TEXT UNIQUE NOT NULL,
            config_value TEXT NOT NULL,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # Create indexes for better performance
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_detections_camera_time ON detection_results(camera_id, timestamp)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_alerts_camera_status ON alerts(camera_id, status)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_alerts_priority ON alerts(priority)')
    
    def save_camera_config(self, camera_id: str, config: Dict[str, Any]) -> bool:
        """Save camera configuration
        
        Args:
            camera_id: Camera identifier
            config: Camera configuration
            
        Returns:
            True if successful, False otherwise
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                config_json = json.dumps(config)
                current_time = datetime.now().isoformat()
                
                # Try to update existing config
                cursor.execute(
                    "UPDATE camera_configs SET config_json = ?, updated_at = ? WHERE camera_id = ?",
                    (config_json, current_time, camera_id)
                )
                
                # If no update, insert new record
                if cursor.rowcount == 0:
                    cursor.execute(
                        "INSERT INTO camera_configs (camera_id, config_json, created_at, updated_at) VALUES (?, ?, ?, ?)",
                        (camera_id, config_json, current_time, current_time)
                    )
                
                conn.commit()
                conn.close()
                
            logger.info(f"Saved configuration for camera {camera_id}")
            return True
            
        except Exception as e:
            logger.error(f"Error saving camera config for {camera_id}: {str(e)}")
            return False
    
    def get_camera_config(self, camera_id: str) -> Optional[Dict[str, Any]]:
        """Get camera configuration
        
        Args:
            camera_id: Camera identifier
            
        Returns:
            Camera configuration or None if not found
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                cursor.execute(
                    "SELECT config_json FROM camera_configs WHERE camera_id = ?",
                    (camera_id,)
                )
                
                row = cursor.fetchone()
                conn.close()
                
                if row:
                    return json.loads(row[0])
                
                return None
                
        except Exception as e:
            logger.error(f"Error getting camera config for {camera_id}: {str(e)}")
            return None
    
    def save_detection_result(self, camera_id: str, timestamp: float, 
                           objects: List[Dict], frame_number: int = None) -> bool:
        """Save detection result
        
        Args:
            camera_id: Camera identifier
            timestamp: Detection timestamp
            objects: List of detected objects
            frame_number: Optional frame number
            
        Returns:
            True if successful, False otherwise
        """
        try:
            # Create summary
            class_counts = {}
            for obj in objects:
                class_name = obj.get('class_name', 'unknown')
                class_counts[class_name] = class_counts.get(class_name, 0) + 1
            
            summary = f"Detected {len(objects)} objects: " + \
                     ", ".join([f"{count} {cls}" for cls, count in class_counts.items()])
            
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                cursor.execute(
                    "INSERT INTO detection_results (camera_id, timestamp, frame_number, objects_json, summary) "
                    "VALUES (?, ?, ?, ?, ?)",
                    (camera_id, timestamp, frame_number, json.dumps(objects), summary)
                )
                
                conn.commit()
                conn.close()
                
            logger.debug(f"Saved detection result for camera {camera_id}: {summary}")
            return True
            
        except Exception as e:
            logger.error(f"Error saving detection result for {camera_id}: {str(e)}")
            return False
    
    def get_detection_history(self, camera_id: str, limit: int = 100, 
                            start_time: float = None, end_time: float = None) -> List[Dict]:
        """Get detection history
        
        Args:
            camera_id: Camera identifier
            limit: Maximum number of results
            start_time: Optional start time
            end_time: Optional end time
            
        Returns:
            List of detection results
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                query = "SELECT id, camera_id, timestamp, frame_number, objects_json, summary FROM detection_results "
                params = []
                
                if camera_id:
                    query += "WHERE camera_id = ? "
                    params.append(camera_id)
                
                if start_time:
                    if camera_id:
                        query += "AND timestamp >= ? "
                    else:
                        query += "WHERE timestamp >= ? "
                    params.append(start_time)
                
                if end_time:
                    if 'WHERE' in query:
                        query += "AND timestamp <= ? "
                    else:
                        query += "WHERE timestamp <= ? "
                    params.append(end_time)
                
                query += "ORDER BY timestamp DESC LIMIT ?"
                params.append(limit)
                
                cursor.execute(query, params)
                rows = cursor.fetchall()
                conn.close()
                
                results = []
                for row in rows:
                    results.append({
                        'id': row[0],
                        'camera_id': row[1],
                        'timestamp': row[2],
                        'frame_number': row[3],
                        'objects': json.loads(row[4]),
                        'summary': row[5]
                    })
                
                return results
                
        except Exception as e:
            logger.error(f"Error getting detection history for {camera_id}: {str(e)}")
            return []
    
    def save_alert(self, alert: Dict[str, Any]) -> bool:
        """Save alert to database
        
        Args:
            alert: Alert data
            
        Returns:
            True if successful, False otherwise
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                # Extract metadata (all fields except core alert fields)
                core_fields = {'alert_id', 'camera_id', 'violation_type', 'object_id', 
                             'object_class', 'message', 'priority', 'status', 
                             'timestamp', 'created_at', 'acknowledged_at', 'resolved_at'}
                metadata = {k: v for k, v in alert.items() if k not in core_fields}
                
                cursor.execute(
                    "INSERT OR REPLACE INTO alerts "
                    "(alert_id, camera_id, violation_type, object_id, object_class, "
                    " message, priority, status, timestamp, created_at, "
                    " acknowledged_at, resolved_at, metadata_json) "
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    (
                        alert.get('alert_id'),
                        alert.get('camera_id'),
                        alert.get('violation_type'),
                        alert.get('object_id'),
                        alert.get('object_class'),
                        alert.get('message'),
                        alert.get('priority', 'low'),
                        alert.get('status', 'active'),
                        alert.get('timestamp'),
                        alert.get('created_at'),
                        alert.get('acknowledged_at'),
                        alert.get('resolved_at'),
                        json.dumps(metadata) if metadata else '{}'
                    )
                )
                
                conn.commit()
                conn.close()
                
            logger.info(f"Saved alert {alert.get('alert_id')} to database")
            return True
            
        except Exception as e:
            logger.error(f"Error saving alert {alert.get('alert_id')}: {str(e)}")
            return False
    
    def update_alert_status(self, alert_id: str, status: str) -> bool:
        """Update alert status
        
        Args:
            alert_id: Alert identifier
            status: New status
            
        Returns:
            True if successful, False otherwise
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                current_time = datetime.now().isoformat()
                
                # Update status and relevant timestamp
                if status == 'acknowledged':
                    cursor.execute(
                        "UPDATE alerts SET status = ?, acknowledged_at = ? WHERE alert_id = ?",
                        (status, current_time, alert_id)
                    )
                elif status == 'resolved':
                    cursor.execute(
                        "UPDATE alerts SET status = ?, resolved_at = ? WHERE alert_id = ?",
                        (status, current_time, alert_id)
                    )
                else:
                    cursor.execute(
                        "UPDATE alerts SET status = ? WHERE alert_id = ?",
                        (status, alert_id)
                    )
                
                conn.commit()
                conn.close()
                
                if cursor.rowcount > 0:
                    logger.info(f"Updated alert {alert_id} status to {status}")
                    return True
                else:
                    logger.warning(f"Alert {alert_id} not found for status update")
                    return False
                    
        except Exception as e:
            logger.error(f"Error updating alert {alert_id} status: {str(e)}")
            return False
    
    def get_alerts(self, camera_id: str = None, status: str = None, 
                  limit: int = 100, priority: str = None) -> List[Dict]:
        """Get alerts with filters
        
        Args:
            camera_id: Optional camera filter
            status: Optional status filter
            limit: Maximum number of results
            priority: Optional priority filter
            
        Returns:
            List of alerts
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                query = "SELECT alert_id, camera_id, violation_type, object_id, object_class, "
                query += " message, priority, status, timestamp, created_at, "
                query += " acknowledged_at, resolved_at, metadata_json FROM alerts "
                
                params = []
                conditions = []
                
                if camera_id:
                    conditions.append("camera_id = ?")
                    params.append(camera_id)
                
                if status:
                    conditions.append("status = ?")
                    params.append(status)
                
                if priority:
                    conditions.append("priority = ?")
                    params.append(priority)
                
                if conditions:
                    query += "WHERE " + " AND ".join(conditions)
                
                query += " ORDER BY timestamp DESC LIMIT ?"
                params.append(limit)
                
                cursor.execute(query, params)
                rows = cursor.fetchall()
                conn.close()
                
                alerts = []
                for row in rows:
                    alert = {
                        'alert_id': row[0],
                        'camera_id': row[1],
                        'violation_type': row[2],
                        'object_id': row[3],
                        'object_class': row[4],
                        'message': row[5],
                        'priority': row[6],
                        'status': row[7],
                        'timestamp': row[8],
                        'created_at': row[9],
                        'acknowledged_at': row[10],
                        'resolved_at': row[11]
                    }
                    
                    # Add metadata
                    metadata = json.loads(row[12])
                    alert.update(metadata)
                    
                    alerts.append(alert)
                
                return alerts
                
        except Exception as e:
            logger.error(f"Error getting alerts: {str(e)}")
            return []
    
    def save_system_config(self, config_key: str, config_value: Any) -> bool:
        """Save system configuration
        
        Args:
            config_key: Configuration key
            config_value: Configuration value (will be JSON encoded)
            
        Returns:
            True if successful, False otherwise
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                # Convert to string if not already
                if not isinstance(config_value, str):
                    config_value = json.dumps(config_value)
                
                current_time = datetime.now().isoformat()
                
                # Try to update existing
                cursor.execute(
                    "UPDATE system_config SET config_value = ?, updated_at = ? WHERE config_key = ?",
                    (config_value, current_time, config_key)
                )
                
                # If no update, insert new
                if cursor.rowcount == 0:
                    cursor.execute(
                        "INSERT INTO system_config (config_key, config_value, updated_at) VALUES (?, ?, ?)",
                        (config_key, config_value, current_time)
                    )
                
                conn.commit()
                conn.close()
                
            logger.info(f"Saved system config: {config_key}")
            return True
            
        except Exception as e:
            logger.error(f"Error saving system config {config_key}: {str(e)}")
            return False
    
    def get_system_config(self, config_key: str) -> Optional[Any]:
        """Get system configuration
        
        Args:
            config_key: Configuration key
            
        Returns:
            Configuration value or None if not found
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                cursor.execute(
                    "SELECT config_value FROM system_config WHERE config_key = ?",
                    (config_key,)
                )
                
                row = cursor.fetchone()
                conn.close()
                
                if row:
                    # Try to parse as JSON
                    try:
                        return json.loads(row[0])
                    except json.JSONDecodeError:
                        # If not JSON, return as string
                        return row[0]
                
                return None
                
        except Exception as e:
            logger.error(f"Error getting system config {config_key}: {str(e)}")
            return None
    
    def cleanup_old_data(self, detection_days: int = 30, alert_days: int = 90):
        """Clean up old data to maintain database size
        
        Args:
            detection_days: Days to keep detection data
            alert_days: Days to keep alert data
        """
        try:
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                # Calculate cutoff times
                current_time = time.time()
                detection_cutoff = current_time - (detection_days * 24 * 3600)
                alert_cutoff = current_time - (alert_days * 24 * 3600)
                
                # Delete old detection data
                cursor.execute(
                    "DELETE FROM detection_results WHERE timestamp < ?",
                    (detection_cutoff,)
                )
                detection_deleted = cursor.rowcount
                
                # Delete old resolved alerts
                cursor.execute(
                    "DELETE FROM alerts WHERE timestamp < ? AND status = 'resolved'",
                    (alert_cutoff,)
                )
                alert_deleted = cursor.rowcount
                
                conn.commit()
                conn.close()
                
            logger.info(f"Cleaned up {detection_deleted} old detection records and {alert_deleted} old alerts")
            
        except Exception as e:
            logger.error(f"Error cleaning up old data: {str(e)}")
            
    def add_system_log(self, level: str, component: str, message: str, 
                      details: Dict = None, stack_trace: str = None, metadata: Dict = None) -> bool:
        """添加系统日志
        
        Args:
            level: 日志级别
            component: 组件名称
            message: 日志消息
            details: 详细信息
            stack_trace: 堆栈跟踪
            metadata: 元数据，存储额外的上下文信息
            
        Returns:
            True if successful, False otherwise
        """
        try:
            # 直接记录日志到数据库（需要先确保有system_logs表）
            with self.connection_lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()
                
                # 确保system_logs表存在
                cursor.execute('''
                CREATE TABLE IF NOT EXISTS system_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
                    level TEXT NOT NULL,
                    component TEXT NOT NULL,
                    message TEXT NOT NULL,
                    details TEXT,
                    stack_trace TEXT,
                    log_metadata TEXT
                )
                ''')
                
                # 插入日志记录
                cursor.execute(
                    "INSERT INTO system_logs (level, component, message, details, stack_trace, log_metadata) "
                    "VALUES (?, ?, ?, ?, ?, ?)",
                    (
                        level,
                        component,
                        message,
                        json.dumps(details) if details else None,
                        stack_trace,
                        json.dumps(metadata) if metadata else None
                    )
                )
                
                conn.commit()
                conn.close()
                
            logger.debug(f"添加系统日志成功: {level} - {component} - {message}")
            return True
        except Exception as e:
            logger.error(f"添加系统日志失败: {str(e)}")
            import traceback
            logger.error(f"详细错误堆栈: {traceback.format_exc()}")
            return False


def get_database_manager(db_path: str = "./yolo_apply.db") -> DatabaseManager:
    """Get database manager instance
    
    Args:
        db_path: Path to SQLite database file
        
    Returns:
        DatabaseManager instance
    """
    if not hasattr(get_database_manager, '_instance'):
        get_database_manager._instance = DatabaseManager(db_path)
    return get_database_manager._instance